#!usr/bin/python
# -*- coding: utf-8 -*-
print('对mongodb增加字段')

import xlrd
import pymongo
import pandas as pd
import re

word=pd.read_excel("C:\\Users\\Administrator\\Desktop\\专家词库.xlsx",encoding='gbk')
columns=['英文名称','中文名称']
words=pd.DataFrame(word,columns=columns)
# print(words)

connection=pymongo.MongoClient('mongodb://192.168.1.123:27017')
db=connection.test
collection=db.aminer_authors
print('数据库连接成功')

# for index,row in words.iterrows():
#     # print(row['英文名称'])
#     regex='^'+str(row['英文名称'])
#     print(regex)

for index,row in words.iterrows():
    # 注意这里的名字必须是精准匹配,使用正则表达式,^字符串开始位置,$字符串结束位置
    regex="^"+str(row['英文名称'])+"$"
    # cursor=collection.find({"name":{"$regex":regex}})   # 对name进行模糊查询
    # cursor=collection.find({"name":re.compile(str(row['英文名称']),re.IGNORECASE)})
    cursor=collection.find({"name":{'$regex':regex,'$options':'i'}})
    # 查询考虑: 正序查询、倒序查询、若均无结果，将赋值为Null
    # 正序查询匹配
    if (cursor is not None):
        for document in cursor:
            db.aminer_authors.update_one({'_id':document['_id']},{'$set':{'chiname':row['中文名称']}})   # 对数据库进行更新
    # 倒序查询匹配
    strr=str(row['英文名称']).split(' ')
    ls=list(strr)
    ls.reverse()
    engname=" ".join(i for i in ls)
    # 查询时忽略大小写

    regex1="^"+engname+"$"
    # cursor=collection.find({"name":re.compile(regex1,re.IGNORECASE)})
    cursor=collection.find({"name":{'$regex':regex1,'$options':'i'}})
    if (cursor is not None):
        for document in cursor:
            db.aminer_authors.update_one({'_id':document['_id']},{'$set':{'chiname':row['中文名称']}})
    else:
        continue

cursor=collection.find({'chiname':{'$exists':False}})
for document in cursor:
    db.aminer_authors.update_one({'_id':document['_id']},{'$set':{'chiname':""}})
